package com.forward.mobile.optitrans.db;

import com.forward.mobile.optitrans.domain.Arret;
import com.forward.mobile.optitrans.domain.Chauffeur;
import com.forward.mobile.optitrans.domain.ClientParam;
import com.forward.mobile.optitrans.domain.Device;
import com.forward.mobile.optitrans.domain.DeviceParam;
import com.forward.mobile.optitrans.domain.Position;
import com.forward.mobile.optitrans.domain.Vehicule;
import com.forward.mobile.optitrans.util.GsonUtils;
import com.forward.mobile.optitrans.util.SpeedStateRule;
import com.forward.mobile.optitrans.util.Utils;
import com.google.gson.JsonObject;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import org.json.JSONException;

public class DBSource {

    private Connector connector;

    public DBSource() throws Exception {
        this.connector = new Connector();

    }
    public static DBSource instance;

    public static DBSource getInstance() throws Exception {
        if (instance == null) {
            instance = new DBSource();
        }
        return instance;
    }

    public void open() throws SQLException {

        connector.open();

    }

    public void close() {
        try {
            connector.close();
        } catch (Exception e) {
        }
    }

    public List<Chauffeur> findChauffeur(int idclient) throws SQLException {
        List<Chauffeur> chauffeurs = new ArrayList<Chauffeur>();
        String request = "select *from chauffeur where id_client=?";
        PreparedStatement preparedStatement = connector.getPrepareStatement(request);
        preparedStatement.setInt(1, idclient);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            chauffeurs.add(new Chauffeur(resultSet));
        }

        return chauffeurs;
    }

    public List<Vehicule> findVehicule(int idclient) throws SQLException {
        List<Vehicule> vehicules = new ArrayList<Vehicule>();
        String request = "select *from vehicule where id_client=?";
        PreparedStatement preparedStatement = connector.getPrepareStatement(request);
        preparedStatement.setInt(1, idclient);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            vehicules.add(new Vehicule(resultSet));
        }
        return vehicules;
    }

    public ClientParam findClientParam(int idclient) throws SQLException {

        String request = "select *from client_param where id_client=?";
        PreparedStatement preparedStatement = connector.getPrepareStatement(request);
        preparedStatement.setInt(1, idclient);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            return new ClientParam(resultSet);
        }
        return null;
    }

    public Device findDevice(String uuid) throws SQLException {
        String request = "select *from device where uuid=?";
        PreparedStatement preparedStatement = connector.getPrepareStatement(request);
        preparedStatement.setString(1, uuid);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            return new Device(resultSet);
        }
        return null;
    }

    public JsonObject findClientParam(String uuid) throws SQLException, JSONException {
        Device device = findDevice(uuid);
        int idClient = device.getIdClient();
        List<Chauffeur> chauffeurs = findChauffeur(idClient);
        List<Vehicule> vehicules = findVehicule(idClient);
        ClientParam clientParam = findClientParam(idClient);
        
        
        return GsonUtils.toGson(clientParam, vehicules, chauffeurs);

    }

    public DeviceParam findDeviceParam(String uuid) {
        try {
            PreparedStatement preparedStatement = connector.getPrepareStatement(Utils.deviceRequest());
            preparedStatement.setString(1, uuid);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return new DeviceParam(resultSet);
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;

        }

    }

    public void saveStartArret(List<Arret> arrets) throws SQLException {
        String request = "insert into arret (id_device,uuid,id_vehicule,id_chauffeur,start_time,end_time,latitude,longitude,duree) values(?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connector.getPrepareStatement(request);
        for (Arret arret : arrets) {
            preparedStatement.setInt(1, arret.getIdDevice());
            preparedStatement.setString(2, arret.getUuid());
            preparedStatement.setInt(3, arret.getIdVehicule());
            preparedStatement.setInt(4, arret.getIdChauffeur());
            preparedStatement.setTimestamp(5, new Timestamp(arret.getStart()));
            preparedStatement.setTimestamp(6, new Timestamp(arret.getEnd()));
            preparedStatement.setDouble(7, arret.getLatitude());
            preparedStatement.setDouble(8, arret.getLongitude());
            preparedStatement.setLong(9, arret.getDuree());
            preparedStatement.execute();
        }
    }

    public void updateOrInsertPosition(List<Position> positions, DeviceParam deviceParam) throws SQLException {
        String requeste = Utils.updateReques();
        if (positions == null || positions.isEmpty()) {
            return;
        }
        Position position = positions.get(positions.size() - 1);
        String data = Utils.toStringFormat(positions);
        PreparedStatement preparedStatement = connector.getPrepareStatement(requeste);
        double vitesse = position.getVitesse();
        preparedStatement.setDouble(1, position.getLatitude());
        preparedStatement.setDouble(2, position.getLongitude());
        preparedStatement.setDouble(3, vitesse);
        preparedStatement.setString(4, SpeedStateRule.getState(vitesse).name());
        preparedStatement.setLong(5, position.getDateGPS());
        preparedStatement.setString(6, data);
        preparedStatement.setInt(7, deviceParam.getIdDevice());
        preparedStatement.setInt(8, deviceParam.getIdVehicule());
        preparedStatement.setInt(9, deviceParam.getIdChauffeur());
        preparedStatement.setTimestamp(10, deviceParam.getTimestamp());
        preparedStatement.execute();
    }
}
